﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DiemDanh
    {
        //private const String SConn = "Data Source=DUCLINH-PC;Initial Catalog=HOUSMS;Persist Security Info=True;User ID=sa;Password=minhduc";
        private static string SConn = ConfigurationManager.ConnectionStrings["DucLM"].ConnectionString;
        DataSet ds = new DataSet();
        SqlConnection conn = new SqlConnection(SConn);
        #region "Lấy số lần điểm danh của sinh viên"
        public int GetDiemDanh(string tenLop, string maSV, int maMonHoc, int soBuoi)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "EXECUTE @diemDanh = GetDiemDanh @TenLop, " + "@MaSV, " + "@MaMonHoc, " + "@SoBuoi";
            try
            {
                cmd.Parameters.AddWithValue("@TenLop", tenLop);
                cmd.Parameters.AddWithValue("@MaSV", maSV);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                cmd.Parameters.AddWithValue("@SoBuoi", soBuoi);
                cmd.Parameters.AddWithValue("@diemDanh", SqlDbType.Int);
                cmd.Parameters["@diemDanh"].Direction = ParameterDirection.Output;
                SqlDataReader reader = cmd.ExecuteReader();

                while(reader.Read())
                {
                    Console.WriteLine("reader[\"TenLop\"]=" + reader["TenLop"]);
                    Console.WriteLine("reader[\"MaSV\"]=" + reader["MaSV"]);
                    Console.WriteLine("reader[\"MaMonHoc\"]=" + reader["MaMonHoc"]);
                    Console.WriteLine("reader[\"SoBuoi\"]=" + reader["SoBuoi"]);
                }
                reader.Close();
                return (int) cmd.Parameters["@diemDanh"].Value;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Thêm mới điểm danh"
        public DataSet InsertDiemDanh(string tenLop, string maSV, int maMonHoc, int diemDanh, int soBuoi)
        {
            var da = new SqlDataAdapter("InsertDiemDanh", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenLop", tenLop);
            da.SelectCommand.Parameters.AddWithValue("@MaSV", maSV);
            da.SelectCommand.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
            da.SelectCommand.Parameters.AddWithValue("@DiemDanh", diemDanh);
            da.SelectCommand.Parameters.AddWithValue("@SoBuoi", soBuoi);
            da.Fill(ds, "InsertDiemDanh");
            return ds;
        }
        #endregion

        #region "Lấy số buổi đã điểm danh"
        public int GetSoBuoi(string tenLop, int maMonHoc)
        {
            SqlConnection conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "EXECUTE @soBuoi = GetSoBuoi @TenLop, " + "@MaMonHoc";
            try
            {
                cmd.Parameters.AddWithValue("@TenLop", tenLop);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                cmd.Parameters.Add("@soBuoi", SqlDbType.Int);
                cmd.Parameters["@soBuoi"].Direction = ParameterDirection.Output;
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    Console.WriteLine("reader[\"TenLop\"]=" + reader["TenLop"]);
                    Console.WriteLine("reader[\"MaMonHoc\"]=" + reader["MaMonHoc"]);
                }
                return (int) cmd.Parameters["@soBuoi"].Value;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Lấy tổng số buổi sinh viên được điểm danh"
        public int GetBuoiDiHoc(string maSV, int maMonHoc)
        {
            SqlConnection conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "EXECUTE @total = GetBuoiDiHoc @MaSV, " + "@MaMonHoc";
            try
            {
                cmd.Parameters.AddWithValue("@MaSV", maSV);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                cmd.Parameters.Add("@total", SqlDbType.Int);
                cmd.Parameters["@total"].Direction = ParameterDirection.Output;
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    Console.WriteLine("reader[\"MaSV\"]=" + reader["MaSV"]);
                    Console.WriteLine("reader[\"MaMonHoc\"]=" + reader["MaMonHoc"]);
                }
                return (int) cmd.Parameters["@total"].Value;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion
    }
}
